package com.sptci.rwt;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Collection;

import com.sptci.util.CloseJDBCResources;

/**
 * An analyser for analysing procedure type objects in the database.
 *
 * <p>&copy; Copyright 2007 Sans Pareil Technologies, Inc.</p>
 * @author Rakesh Vidyadharan 2007-09-27
 * @version $Id: ProcedureAnalyser.java 2 2007-10-19 21:06:36Z rakesh.vidyadharan $
 */
public class ProcedureAnalyser extends Analyser
{
  /**
   * Create a new instance of the class using the specified connection
   * manager.
   *
   * @param manager The manager for obtaining database connections.
   */
  public ProcedureAnalyser( final ConnectionManager manager )
  {
    super( manager );
  }

  /**
   * Returns a collection of {@link ProcedureMetaData} objects that contain the
   * basic information pertaining to the procedures in the schema.
   *
   * @see Analyser#analyse
   * @param parameters Must contain one parameter which is a {@link
   *   SchemaMetaData} that represents the <code>schema</code> to restrict
   *   the analysis to.
   */
  @Override
  public Collection<ProcedureMetaData> analyse(
      final MetaData... parameters ) throws SQLException
  {
    final Collection<ProcedureMetaData> collection =
      new ArrayList<ProcedureMetaData>();
    final String query = "select data_type, type_udt_name, routine_body, routine_definition from information_schema.routines where routine_schema = ? and routine_name = ?";

    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try
    {
      String schema = ( parameters[0] == null ) ? null : parameters[0].getName();
      connection = manager.open();
      statement = connection.prepareStatement( query );

      DatabaseMetaData dmd = connection.getMetaData();
      resultSet = dmd.getProcedures( null, schema, "%" );

      while ( resultSet.next() )
      {
        ProcedureMetaData pmd = new ProcedureMetaData();

        pmd.setName( resultSet.getString( "procedure_name" ) );
        pmd.setComment( resultSet.getString( "remarks" ) );

        getAdditionalAttributes(
            pmd, (SchemaMetaData) parameters[0], statement, connection );

        if ( parameters[0] != null )
        {
          pmd.setSchema( (SchemaMetaData) parameters[0] );
        }

        collection.add( pmd );
      }

      if ( parameters[0] != null )
      {
        ( (SchemaMetaData) parameters[0] ).setProcedures( collection );
      }
    }
    finally
    {
      CloseJDBCResources.close( resultSet );
      CloseJDBCResources.close( statement );
      CloseJDBCResources.close( connection );
    }

    return collection;
  }

  /**
   * Fetch additional information about the procedure from the
   * <code>Information Schema</code>.
   *
   * <p><b>Note:</b> Over-ridden procedures are not handled properly.</p>
   *
   * @param pmd The metadata object that is to be updated.
   * @param smd The schema metadata object.
   * @param statement The statement to execute
   * @param connection The database connection to use.
   * @throws SQLException If errors are encountered while fetching the
   *   information.
   */
  protected void getAdditionalAttributes( final ProcedureMetaData pmd,
      final SchemaMetaData smd, final PreparedStatement statement,
      final Connection connection ) throws SQLException
  {
    ResultSet resultSet = null;

    try
    {
      statement.clearParameters();
      statement.setString( 1, smd.getName() );
      statement.setString( 2, pmd.getName() );

      resultSet = statement.executeQuery();
      if ( resultSet.next() )
      {
        pmd.setDataType( resultSet.getString( "data_type" ) );
        pmd.setUserDefinedType( resultSet.getString( "type_udt_name" ) );
        pmd.setBody( resultSet.getString( "routine_body" ) );
        pmd.setDefinition( resultSet.getString( "routine_definition" ) );
      }
    }
    finally
    {
      CloseJDBCResources.close( resultSet );
    }
  }
}
